using Dapper;
using log4net;
using System;
using System.Collections.Generic;
using System.Data;

/**
*┌──────────────────────────────────────────────────────────────┐
*│　描    述：库存盘点主表接口实现
*│　作    者：李宝
*│　版    本：1.0 模板代码自动生成
*│　创建时间：2019-01-05 10:49:56
*└──────────────────────────────────────────────────────────────┘
*┌──────────────────────────────────────────────────────────────┐
*│　命名空间： Usido.CRM.Repository
*│　类    名： StockInventoryRepository
*└──────────────────────────────────────────────────────────────┘
*/

using Usido.CRM.Core.DbHelper;
using Usido.CRM.Core.Extensions;
using Usido.CRM.Core.Options;
using Usido.CRM.Core.Repository;
using Usido.CRM.Core.ToolHelper;
using Usido.CRM.Dto;
using Usido.CRM.IRepository;
using Usido.CRM.Models;

namespace Usido.CRM.Repository
{
    public class StockInventoryRepository : BaseRepository<StockInventory, int>, IStockInventoryRepository
    {
        private readonly ILog logger = LogManager.GetLogger(nameof(SupplyOrderRepository));
        public StockInventoryRepository()
        {
            _dbConnection = ConnectionFactory.CreateConnection(DbOptionData.DbType, DbOptionData.ConnectionString);
        }

        public int AddInventory(StockInventory inventory, List<StockInventoryDetail> list)
        {
            IDbTransaction transaction;
            transaction = _dbConnection.BeginTransaction();

            string sqlAddInventory = @"
INSERT INTO dbo.StockInventory(OrderCode, UserId, CreateTime, State,Remark)
VALUES(@OrderCode, @UserId, GETDATE(), 0,@Remark)
SELECT @@IDENTITY";

            string sqlAddInventoryInfo = @"
INSERT INTO dbo.StockInventoryDetail(OrderId,ProductId,BatchNumber,StockQuantity,ActualQuantity,InventorySurplusQuantity,DateExpiry)
VALUES(@OrderId,@ProductId,@BatchNumber,@StockQuantity,@ActualQuantity,@InventorySurplusQuantity,@DateExpiry)";
            try
            {
                inventory.OrderCode = Tools.GetOrderCode(5);
                int orderId = _dbConnection.ExecuteScalar<int>(sqlAddInventory, inventory, transaction);
                if (orderId <= 0)
                {
                    transaction.Rollback();
                    return -1;
                }

                foreach (var item in list)
                {
                    item.OrderId = orderId;
                    _dbConnection.Execute(sqlAddInventoryInfo, item, transaction);
                }

                transaction.Commit();

                return orderId;
            }
            catch (Exception ex)
            {
                logger.Error("添加库存盘点信息时出错", ex);
                transaction.Rollback();

                return -1;
            }
        }

        public bool UpdateInventory(StockInventory inventory, List<StockInventoryDetail> list)
        {
            IDbTransaction transaction;
            transaction = _dbConnection.BeginTransaction();

            string sqlUpdateSupply = @"UPDATE dbo.StockInventory SET Remark = @Remark WHERE Id = @Id";
            string sqlDeleteSupplyInfo = @"DELETE dbo.StockInventoryDetail WHERE OrderId = @OrderId";
            string sqlUpdateSupplyInfo = @"
INSERT INTO dbo.StockInventoryDetail(OrderId,ProductId,BatchNumber,StockQuantity,ActualQuantity,InventorySurplusQuantity,DateExpiry)
VALUES(@OrderId,@ProductId,@BatchNumber,@StockQuantity,@ActualQuantity,@InventorySurplusQuantity,@DateExpiry)
";
            try
            {
                _dbConnection.Execute(sqlUpdateSupply, inventory, transaction);
                _dbConnection.Execute(sqlDeleteSupplyInfo, new { OrderId = inventory.Id }, transaction);

                foreach (var item in list)
                {
                    _dbConnection.Execute(sqlUpdateSupplyInfo, item, transaction);
                }

                transaction.Commit();

                return true;
            }
            catch (Exception ex)
            {
                logger.Error("修改库存盘点时出错", ex);
                transaction.Rollback();

                return false;
            }
        }

        public bool DeleteInventory(int id)
        {
            string sql = @"DELETE dbo.StockInventory WHERE Id = @OrderId
DELETE dbo.StockInventoryDetail WHERE OrderId = @OrderId";

            return _dbConnection.Execute(sql, new { OrderId = id }) > 0;
        }

        public List<StockInventoryDetailDto> GetInventoryInfoList(int orderId)
        {
            string sql = @"SELECT B.ProductName,B.ProductCode,B.Specification,B.ManufacturingEnterprise,
B.MarketingUnit, A.*
FROM dbo.StockInventoryDetail AS A
INNER JOIN dbo.SysProduct AS B ON A.ProductId = B.Id
WHERE A.OrderId = @OrderId ";

            return _dbConnection.Query<StockInventoryDetailDto>(sql, new { OrderId = orderId }).AsList();
        }

        public List<StockInventoryDto> GetInventoryList(string where = "")
        {
            string sql = @"SELECT * FROM (
    SELECT A.*,B.UserName FROM dbo.StockInventory AS A
    INNER JOIN dbo.SysUser AS B ON A.UserId = B.Id
) T ";
            if (!where.IsNullOrEmpty())
            {
                sql += where;
            }

            return _dbConnection.Query<StockInventoryDto>(sql).AsList();
        }

        public StockInventoryDto GetModel(int id)
        {
            string sql = @"SELECT * FROM (
    SELECT A.*,B.UserName FROM dbo.StockInventory AS A
    INNER JOIN dbo.SysUser AS B ON A.UserId = B.Id
) T WHERE Id = @Id ";

            return _dbConnection.QueryFirstOrDefault<StockInventoryDto>(sql, new { Id = id });
        }

        public bool InStore(int orderId, int userId)
        {
            var result = _dbConnection.ExecuteScalar<int>("P_Inventory", new { UserId = userId, OrderId = orderId }, commandType: CommandType.StoredProcedure);

            return result == 1;
        }
    }
}